TAD's Quick ASP/Database tut 4
SQL - Queries
The SQL (Structure Query Language) is a powerful (and sometimes annoying) tool through which we can insert, delete, select or update records. The syntax can sometimes be difficult to follow especially when concatenating lots of literal strings with variables and dealing with the 'quoting' issues. I suggest creating some some global functions to handle these small tasks and make your code easier to read and write :) See the bonus .ZIP file for more info.
QUOTING SQL Parameters
There are 4 different types of 'quoting' which must take place in order to keep the SQL monster happy, in general it boils down to placing strings inside (') single quotes, dates inside (#) hash characters, LIKE conditional patterns inside ('% and %') percentages and numbers inside nothing.
Here are some small function that should hopefully make your code neat n tidy.
Function QNUM(n)
If isNumeric(n) Then QNUM=n Else QNUM=""
End Function
Function QSTRING(s)
QSTRING = "'" &emp; Replace(s, "'", "''") &emp; "'"
End Function
Function QDATE(d)
QDATE = "#" &emp; FormatDateTime(d,0) &emp; "#"
End Function
Function QLIKE(s)
QLIKE = "'%" &emp; Replace(s,"'","''") &emp; "%'"
End Function
The code above should be straight forward (you may wish to extend the functions to check for Null or Empty variables or pre-check the values before attempting to use them in your SQL queries). Here is an example of inserting a new record by using the above functions to 'quote' certain data-types.
[Editor's note: Since several lines exceeded the width of the article window, I've had to insert a line-break. However, the code won't run unless you remove the line-break. The lines concerned have therefore been marked with the string <!-- !!! -->. //Adok]
'// some example values //
firstname = "john"
lastname = "doe"
email = "spam@hotmail.com"
created = now
username = "noob"
password = "open 2 hackerz"
'// build the sql statement //
sql = "INSERT INTO UsersTable firstName,lastName,email,
created,userName,password" <!-- !!! -->
sql = sql &emp; " VALUES " &emp; QSTRING(firstname) &emp; "," &emp;QSTRING(lastname)
sql = sql &emp; " , "&emp; QSTRING(email) &emp; "," &emp;QDATE(created)
sql = sql &emp; " , "&emp; QSTRING(username) &emp; "," &emp;QSTRING(password)
'// execute the SQL using the already-open Conn object
Conn.Execute(sql)
Another way to perform the above is by creating a new recordset, setting each value and then using .update( ) to execute the insert.
Dim Conn,RS
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=myDB"
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open "SELECT * FROM UsersTable", Conn, adOpenStatic, adLockPessimistic
RS.AddNew
RS("firstname") = "john"
RS("lastname") = "doe"
RS("email") = "spam@hotmail.com"
RS("created") = now
RS("username") = "noob"
RS("password") = "open 2 hackerz"
RS.Update
SELECT
This is the most useful (and most used) SQL command. The syntax is:
SELECT field(s) FROM table(s) WHERE condition(s) ORDER BY field(s) [ASC|DESC]
Examples:
[1] SELECT * FROM UsersTable
[2] SELECT * FROM UsersTable WHERE ID=4
[3] SELECT * FROM UsersTable WHERE firstName='John'
[4] SELECT * FROM UsersTable WHERE created&emp;lt;= #01/01/2000#
[5] SELECT * FROM UsersTable WHERE firstName LIKE %red% ORDER BY ID
[6] SELECT * FROM UsersTable WHERE firstName='John' And lastName='Doe'
In example [1] it would select ALL records in the UsersTable. Example [2] would only select a single record where the ID=4 (assuming this the ID field is a primary key). Example [3] would select all matching records whose firstName='John' (this would NOT match 'Johnny' or 'John1'). Example [4] would match all records whose created field was before 1st Jan 2000. Example [5] would match all records whose firstName contained 'red' (such as 'Fred', 'Reddy', 'Red', 'Shred' etc..) the results are also sorted by their ID values. Finally, example [6] would select all users where firstName=John and lastName=Doe.
INSERT
One way to insert data into a database is by using the INSERT command. The syntax is:
INSERT INTO table field(s) VALUES value(s)
Examples:
[1] INSERT INTO UsersTable firstName,lastName,email
VALUES 'john','doe','spam@hotmail.com' <!-- !!! -->
[2] INSERT INTO MsgTable parentMsgID VALUES 123
[3] INSERT INTO UsersTable created,userName VALUES #01/02/2003#,'noob'
Example [1] would create a new record in the UsersTable with firstName=John, lastName=Doe, email=spam@hotmail.com. Example [2] would create a new record with parentMsgID=123 and Example [3] creates a new record with created=01/02/2003 and userName=noob.
DELETE
This is the most dangerous command (if you forget to include a WHERE clause that is ;). You can delete individual records by using a unique field value (such as the primary-key field) or entire blocks of records. The syntax is:
DELETE * FROM table WHERE condition(s)
Examples:
[1] DELETE * FROM UserTable WHERE ID=19
[2] DELETE * FROM UserTable WHERE created&emp;lt;=#01/01/2002#
UPDATE
When you have an existing record and want to modify a field of it you can use the UPDATE command. The syntax is:
UPDATE table SET field=value WHERE condition(s)
Example:
[1] UPDATE UsersTable SET userName=firstName WHERE userName=''
Searching
You've already seen how to perform a search (we do it using the SELECT command before we can display any records). Depending on the field you want to search you may use either a '=' in the condition or a LIKE command with '%...%' surrounding your search string.
My advice is, for numbers stick with '=' and for strings use '% ...%' and for login code (where you need to check both username and password you MUST use '=' to compare the entire string!!)
You can also search across multiple record fields using the OR condition.
sql = "SELECT * FROM UsersTable WHERE firstName LIKE '%" &emp; pat &emp; "%'
OR lastName LIKE '%" &emp; pat &emp; "%'" <!-- !!! -->
Conn.Execute(sql)
Once you start building large systems with big databases you might want to consider adding a 'keywords' field to certain tables and then restrict your search to only this keyword field. This way you can categorise your data by using the certain keywords instead of having to search lots of fields.
Tips n Tricks
Once your SQL statements start to get long you'll soon wish for an easier way to construct them without jumping in and out of those damn " and ' quotes. Coders of Perl will be laughing loudly to themselves about now ;)
One simple trick is to ape the Perl $variable way of insering values into literal strings is to use the VBscript Replace command.
sql = "SELECT * FROM $table WHERE firstName LIKE '%$first%'
OR lastName LIKE '%$last%'"
sql = replace(sql, "$table", "UsersTable")
sql = replace(sql, "$first", firstname)
sql = replace(sql, "$second", lastname)
Conn.Execute(sql)
When you're building a flexible search which can have multiple, optional search conditions you can do something like the code below.
'// build the condition(s) //
cond = ""
If searchlast&emp;lt;>"" Then
cond = "lastName LIKE '%" &emp; searchlast &emp; "%'"
End If
If searchfirst&emp;lt;>"" Then
If cond&emp;lt;>"" Then cond = cond &emp; " AND "
cond = cond &emp; "firstName LIKE '%" &emp; searchfirst &emp; "&emp;'"
End If
If isDate(searchDate) Then
If cond&emp;lt;>"" Then cond = cond &emp; " AND "
cond = cond &emp; "createdDate &emp;lt;=#" &emp; searchdate &emp;"#"
End If
'// build the SQL query statement //
If cond&emp;lt;>"" Then
sql = "SELECT * FROM UsersTable WHERE " &emp; cond
Else
sql = "SELECT * FROM UsersTable"
End If
'// Execute the SQL search //
Conn.Execute(sql)
This will construct a SQL search statement using the variables searchfirst, searchlast and searchdate, each variable can be an empty string in which case its value is ignored. You would get these values from 3 different input boxes from your FORM.
Summary
Well, I've only scratched the surface of SQL queries. There are a few more variants on the syntax examples shown above. I've only shown you the most commonly used ones.
'Always start small and then build on it' :)
Next time we will look at how to perform some basic validation and check input strings. Also we'll use a very simple error-message technique to deal with bad input data in a user-friendly way.